pub mod migrations;
pub mod schema;

use sqlx::{SqlitePool, Row, FromRow};
use anyhow::Result;
use tracing::{info, error, debug};
use std::path::PathBuf;

use crate::error::{AppError, Result as AppResult};
use schema::{Device, SocialAccount, Task, TaskLog, SystemConfig, DatabaseStats};

/// 数据库管理器
#[derive(Debug, Clone)]
pub struct DatabaseManager {
    pool: SqlitePool,
}

impl DatabaseManager {
    /// 创建新的数据库管理器实例
    pub async fn new(database_url: &str) -> AppResult<Self> {
        info!("Connecting to database: {}", database_url);
        
        // Extract the database path from the URL
        if let Some(path) = database_url.strip_prefix("sqlite:") {
            // Ensure parent directory exists
            if let Some(parent) = std::path::Path::new(path).parent() {
                std::fs::create_dir_all(parent)?;
            }
            
            // Create the database file if it doesn't exist
            if !std::path::Path::new(path).exists() {
                info!("Creating new database file at: {}", path);
                std::fs::File::create(path)?;
            }
        }
        
        // 创建连接池
        let pool = SqlitePool::connect(database_url).await?;
        
        // 运行迁移
        sqlx::migrate!("./migrations").run(&pool).await
            .map_err(|e| AppError::Migration(e))?;
        
        info!("Database connected and migrated successfully");
        
        Ok(Self { pool })
    }
    
    /// 获取连接池引用
    pub fn pool(&self) -> &SqlitePool {
        &self.pool
    }
    
    /// 健康检查
    pub async fn health_check(&self) -> AppResult<bool> {
        sqlx::query("SELECT 1")
            .fetch_one(&self.pool)
            .await?;
        Ok(true)
    }
    
    /// 获取数据库统计信息
    pub async fn get_stats(&self) -> AppResult<DatabaseStats> {
        let devices_count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM devices")
            .fetch_one(&self.pool)
            .await?;
            
        let accounts_count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM social_accounts")
            .fetch_one(&self.pool)
            .await?;
            
        let tasks_count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM tasks")
            .fetch_one(&self.pool)
            .await?;
            
        let content_count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM content_library")
            .fetch_one(&self.pool)
            .await?;
        
        Ok(DatabaseStats {
            devices_count: devices_count as u64,
            accounts_count: accounts_count as u64,
            tasks_count: tasks_count as u64,
            content_count: content_count as u64,
        })
    }
}

// ============================================
// 设备管理相关数据库操作
// ============================================

impl DatabaseManager {
    /// 创建设备
    pub async fn create_device(&self, device: &Device) -> AppResult<()> {
        debug!("Creating device: {}", device.name);
        
        sqlx::query!(
            r#"
            INSERT INTO devices (
                id, name, device_type, platform, status, created_at, updated_at
            )
            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
            "#,
            device.id,
            device.name,
            device.device_type,
            device.platform,
            device.status,
            device.created_at,
            device.updated_at
        )
        .execute(&self.pool)
        .await?;
        
        info!("Device created successfully: {}", device.name);
        Ok(())
    }
    
    /// 获取所有设备
    pub async fn get_devices(&self) -> AppResult<Vec<Device>> {
        debug!("Fetching all devices");
        
        let rows = sqlx::query!(
            "SELECT id, name, platform, device_type, status, connection_config, device_info, performance_config, proxy_config, last_heartbeat, last_error, uptime_seconds, total_tasks_completed, total_errors, created_at, updated_at FROM devices ORDER BY created_at DESC"
        )
        .fetch_all(&self.pool)
        .await?;
        
        let devices: Vec<Device> = rows.into_iter().map(|row| {
            Device {
                id: row.id.unwrap_or_default(),
                name: row.name,
                device_type: row.device_type,
                platform: row.platform,
                status: row.status,
                connection_config: None,
                device_info: None,
                performance_config: None,
                proxy_config: None,
                last_heartbeat: row.last_heartbeat,
                last_error: None,
                uptime_seconds: None,
                total_tasks_completed: 0,
                total_errors: 0,
                created_at: row.created_at,
                updated_at: row.updated_at,
            }
        }).collect();
        
        debug!("Retrieved {} devices", devices.len());
        Ok(devices)
    }
    
    /// 根据ID获取设备
    pub async fn get_device_by_id(&self, device_id: &str) -> AppResult<Option<Device>> {
        let row = sqlx::query!(
            "SELECT id, name, platform, device_type, status, last_heartbeat, created_at, updated_at FROM devices WHERE id = ?1",
            device_id
        )
        .fetch_optional(&self.pool)
        .await?;
        
        let device = row.map(|row| {
            Device {
                id: row.id.unwrap_or_default(),
                name: row.name,
                device_type: row.device_type,
                platform: row.platform,
                status: row.status,
                connection_config: None,
                device_info: None,
                performance_config: None,
                proxy_config: None,
                last_heartbeat: row.last_heartbeat,
                last_error: None,
                uptime_seconds: None,
                total_tasks_completed: 0,
                total_errors: 0,
                created_at: row.created_at,
                updated_at: row.updated_at,
            }
        });
        
        Ok(device)
    }
    
    /// 更新设备状态
    pub async fn update_device_status(&self, device_id: &str, status: &str) -> AppResult<()> {
        debug!("Updating device status: {} -> {}", device_id, status);
        
        let rows_affected = sqlx::query!(
            r#"
            UPDATE devices 
            SET status = ?1, last_heartbeat = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?2
            "#,
            status,
            device_id
        )
        .execute(&self.pool)
        .await?
        .rows_affected();
        
        if rows_affected == 0 {
            return Err(AppError::NotFound(format!("Device not found: {}", device_id)));
        }
        
        Ok(())
    }
    
    /// 更新设备信息
    pub async fn update_device_info(&self, device_id: &str, device_info: &str) -> AppResult<()> {
        sqlx::query!(
            "UPDATE devices SET device_info = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
            device_info,
            device_id
        )
        .execute(&self.pool)
        .await?;
        
        Ok(())
    }
    
    /// 删除设备
    pub async fn delete_device(&self, device_id: &str) -> AppResult<()> {
        debug!("Deleting device: {}", device_id);
        
        let rows_affected = sqlx::query!(
            "DELETE FROM devices WHERE id = ?1",
            device_id
        )
        .execute(&self.pool)
        .await?
        .rows_affected();
        
        if rows_affected == 0 {
            return Err(AppError::NotFound(format!("Device not found: {}", device_id)));
        }
        
        info!("Device deleted successfully: {}", device_id);
        Ok(())
    }
    
    /// 更新设备心跳
    pub async fn update_device_heartbeat(&self, device_id: &str) -> AppResult<()> {
        sqlx::query!(
            "UPDATE devices SET last_heartbeat = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP WHERE id = ?1",
            device_id
        )
        .execute(&self.pool)
        .await?;
        
        Ok(())
    }
}

// ============================================
// 社媒账号相关数据库操作
// ============================================

impl DatabaseManager {
    /// 创建社媒账号
    pub async fn create_account(&self, account: &SocialAccount) -> AppResult<()> {
        debug!("Creating account: {}@{}", account.username, account.platform);
        
        sqlx::query!(
            r#"
            INSERT INTO social_accounts (
                id, platform, username, email, status, assigned_device_id, account_info, created_at, updated_at
            )
            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
            "#,
            account.id,
            account.platform,
            account.username,
            account.email,
            account.status,
            account.assigned_device_id,
            account.account_info,
            account.created_at,
            account.updated_at
        )
        .execute(&self.pool)
        .await?;
        
        info!("Account created successfully: {}@{}", account.username, account.platform);
        Ok(())
    }
    
    /// 获取所有账号
    pub async fn get_accounts(&self) -> AppResult<Vec<SocialAccount>> {
        debug!("Fetching all accounts");
        
        let rows = sqlx::query!(
            "SELECT id, username, platform, COALESCE(email, '') as email, status, assigned_device_id, COALESCE(account_info, '') as account_info, created_at, updated_at FROM social_accounts ORDER BY created_at DESC"
        )
        .fetch_all(&self.pool)
        .await?;
        
        let accounts: Vec<SocialAccount> = rows.into_iter().map(|row| {
            SocialAccount {
                id: row.id.unwrap_or_default(),
                platform: row.platform,
                username: row.username,
                display_name: None,
                email: if row.email.is_empty() { None } else { Some(row.email) },
                encrypted_credentials: vec![],
                credential_type: "password".to_string(),
                account_info: if row.account_info.is_empty() { None } else { Some(row.account_info) },
                security_settings: None,
                operation_settings: None,
                assigned_device_id: row.assigned_device_id,
                status: row.status,
                last_login: None,
                last_activity: None,
                login_failures: 0,
                created_at: row.created_at,
                updated_at: row.updated_at,
            }
        }).collect();
        
        debug!("Retrieved {} accounts", accounts.len());
        Ok(accounts)
    }
    
    /// 根据平台获取账号
    pub async fn get_accounts_by_platform(&self, platform: &str) -> AppResult<Vec<SocialAccount>> {
        let rows = sqlx::query!(
            "SELECT id, username, platform, COALESCE(email, '') as email, status, assigned_device_id, COALESCE(account_info, '') as account_info, created_at, updated_at FROM social_accounts WHERE platform = ?1 ORDER BY created_at DESC",
            platform
        )
        .fetch_all(&self.pool)
        .await?;
        
        let accounts: Vec<SocialAccount> = rows.into_iter().map(|row| {
            SocialAccount {
                id: row.id.unwrap_or_default(),
                platform: row.platform,
                username: row.username,
                display_name: None,
                email: if row.email.is_empty() { None } else { Some(row.email) },
                encrypted_credentials: vec![],
                credential_type: "password".to_string(),
                account_info: if row.account_info.is_empty() { None } else { Some(row.account_info) },
                security_settings: None,
                operation_settings: None,
                assigned_device_id: row.assigned_device_id,
                status: row.status,
                last_login: None,
                last_activity: None,
                login_failures: 0,
                created_at: row.created_at,
                updated_at: row.updated_at,
            }
        }).collect();
        
        Ok(accounts)
    }
    
    /// 分配账号到设备
    pub async fn assign_account_to_device(&self, account_id: &str, device_id: &str) -> AppResult<()> {
        debug!("Assigning account {} to device {}", account_id, device_id);
        
        let updated_at = chrono::Utc::now().naive_utc();
        let rows_affected = sqlx::query!(
            r#"
            UPDATE social_accounts 
            SET assigned_device_id = ?1, updated_at = ?3
            WHERE id = ?2
            "#,
            device_id,
            account_id,
            updated_at
        )
        .execute(&self.pool)
        .await?
        .rows_affected();
        
        if rows_affected == 0 {
            return Err(AppError::NotFound(format!("Account not found: {}", account_id)));
        }
        
        Ok(())
    }
    
    /// 更新账号最后登录时间
    pub async fn update_account_last_login(&self, account_id: &str) -> AppResult<()> {
        let updated_at = chrono::Utc::now().naive_utc();
        sqlx::query!(
            r#"
            UPDATE social_accounts 
            SET updated_at = ?2
            WHERE id = ?1
            "#,
            account_id,
            updated_at
        )
        .execute(&self.pool)
        .await?;
        
        Ok(())
    }
}

// ============================================
// 任务管理相关数据库操作
// ============================================

impl DatabaseManager {
    /// 创建任务
    pub async fn create_task(&self, task: &Task) -> AppResult<()> {
        debug!("Creating task: {}", task.name);
        
        sqlx::query!(
            r#"
            INSERT INTO tasks (
                id, name, task_type, status, priority, config, device_id, account_id, progress, current_step, created_at, updated_at
            )
            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)
            "#,
            task.id,
            task.name,
            task.task_type,
            task.status,
            task.priority,
            task.config,
            task.device_id,
            task.account_id,
            task.progress_percentage,
            task.current_step,
            task.created_at,
            task.updated_at
        )
        .execute(&self.pool)
        .await?;
        
        info!("Task created successfully: {}", task.name);
        Ok(())
    }
    
    /// 获取任务列表
    pub async fn get_tasks(
        &self,
        status_filter: Option<String>,
        limit: i32,
        offset: i32,
    ) -> AppResult<Vec<Task>> {
        debug!("Fetching tasks with filter: {:?}", status_filter);
        
        // Build dynamic query based on status filter
        let query = if status_filter.is_some() {
            "SELECT id, name, task_type, status, COALESCE(priority, 1) as priority, COALESCE(config, '') as config, COALESCE(output_data, '') as result, device_id, account_id, COALESCE(progress_percentage, 0) as progress, current_step, created_at, updated_at FROM tasks WHERE status = ?1 ORDER BY priority DESC, created_at DESC LIMIT ?2 OFFSET ?3"
        } else {
            "SELECT id, name, task_type, status, COALESCE(priority, 1) as priority, COALESCE(config, '') as config, COALESCE(output_data, '') as result, device_id, account_id, COALESCE(progress_percentage, 0) as progress, current_step, created_at, updated_at FROM tasks ORDER BY priority DESC, created_at DESC LIMIT ?1 OFFSET ?2"
        };
        
        let rows = if let Some(status) = status_filter {
            sqlx::query(query)
                .bind(status)
                .bind(limit)
                .bind(offset)
                .fetch_all(&self.pool)
                .await?
        } else {
            sqlx::query(query)
                .bind(limit)
                .bind(offset)
                .fetch_all(&self.pool)
                .await?
        };
        
        let tasks: Vec<Task> = rows.into_iter().map(|row| {
            Task {
                id: row.get::<Option<String>, _>("id").unwrap_or_default(),
                name: row.get("name"),
                task_type: row.get("task_type"),
                category: "default".to_string(),
                strategy_id: None,
                account_id: row.get::<Option<String>, _>("account_id").unwrap_or_else(|| "unknown".to_string()),
                device_id: row.get("device_id"),
                parent_task_id: None,
                status: row.get("status"),
                priority: row.get("priority"),
                scheduled_at: None,
                deadline: None,
                config: row.get("config"),
                input_data: None,
                output_data: Some(row.get("result")),
                started_at: None,
                completed_at: None,
                execution_duration: None,
                retry_count: 0,
                max_retries: 3,
                retry_delay: 30,
                error_message: None,
                error_code: None,
                error_details: None,
                progress_percentage: row.get("progress"),
                current_step: row.get("current_step"),
                total_steps: 0,
                created_at: row.get("created_at"),
                updated_at: row.get("updated_at"),
            }
        }).collect();
        
        debug!("Retrieved {} tasks", tasks.len());
        Ok(tasks)
    }
    
    /// 更新任务状态
    pub async fn update_task_status(&self, task_id: &str, status: &str) -> AppResult<()> {
        sqlx::query!(
            "UPDATE tasks SET status = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
            status,
            task_id
        )
        .execute(&self.pool)
        .await?;
        
        Ok(())
    }
    
    /// 创建任务日志
    pub async fn create_task_log(&self, log: &TaskLog) -> AppResult<()> {
        sqlx::query!(
            r#"
            INSERT INTO task_logs (task_id, log_level, message, details, step_name, step_index, timestamp)
            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
            "#,
            log.task_id,
            log.log_level,
            log.message,
            log.details,
            log.step_name,
            log.step_index,
            log.timestamp
        )
        .execute(&self.pool)
        .await?;
        
        Ok(())
    }
    
    /// 获取任务日志
    pub async fn get_task_logs(&self, task_id: &str, limit: i32) -> AppResult<Vec<TaskLog>> {
        let rows = sqlx::query!(
            "SELECT id, task_id, log_level, message, COALESCE(details, '') as details, COALESCE(step_name, '') as step_name, COALESCE(step_index, 0) as step_index, timestamp FROM task_logs WHERE task_id = ?1 ORDER BY timestamp DESC LIMIT ?2",
            task_id,
            limit
        )
        .fetch_all(&self.pool)
        .await?;
        
        let logs: Vec<TaskLog> = rows.into_iter().map(|row| {
            TaskLog {
                id: row.id.unwrap_or(0) as i64,
                task_id: row.task_id,
                log_level: row.log_level,
                message: row.message,
                details: if row.details.is_empty() { None } else { Some(row.details) },
                step_name: if row.step_name.is_empty() { None } else { Some(row.step_name) },
                step_index: Some(row.step_index as i64),
                screenshot_path: None,
                attachments: None,
                timestamp: row.timestamp,
            }
        }).collect();
        
        Ok(logs)
    }
}

// ============================================
// 系统配置相关数据库操作
// ============================================

impl DatabaseManager {
    /// 获取系统配置
    pub async fn get_system_config(&self, key: &str) -> AppResult<Option<String>> {
        let value = sqlx::query_scalar!(
            "SELECT config_value FROM system_configs WHERE config_key = ?1",
            key
        )
        .fetch_optional(&self.pool)
        .await?;
        
        Ok(value)
    }
    
    /// 设置系统配置
    pub async fn set_system_config(&self, key: &str, value: &str, description: Option<&str>) -> AppResult<()> {
        sqlx::query!(
            r#"
            INSERT INTO system_configs (config_key, config_value, description)
            VALUES (?1, ?2, ?3)
            ON CONFLICT(config_key) DO UPDATE SET 
            config_value = ?2, 
            description = COALESCE(?3, description),
            updated_at = CURRENT_TIMESTAMP
            "#,
            key,
            value,
            description
        )
        .execute(&self.pool)
        .await?;
        
        Ok(())
    }
    
    /// 获取所有系统配置
    pub async fn get_all_system_configs(&self) -> AppResult<Vec<SystemConfig>> {
        let rows = sqlx::query!(
            "SELECT config_key, config_value, updated_at FROM system_configs ORDER BY config_key"
        )
        .fetch_all(&self.pool)
        .await?;
        
        let configs: Vec<SystemConfig> = rows.into_iter().map(|row| {
            SystemConfig {
                id: 0,
                config_key: row.config_key,
                config_value: row.config_value,
                description: None,
                config_type: "string".to_string(),
                is_encrypted: false,
                created_at: row.updated_at,
                updated_at: row.updated_at,
            }
        }).collect();
        
        Ok(configs)
    }
    
    /// 获取设备统计信息
    pub async fn get_device_stats(&self) -> AppResult<serde_json::Value> {
        let online_count: i64 = sqlx::query_scalar!(
            "SELECT COUNT(*) FROM devices WHERE status = 'online'"
        )
        .fetch_one(&self.pool)
        .await?.into();
        
        let total_count: i64 = sqlx::query_scalar!("SELECT COUNT(*) FROM devices")
            .fetch_one(&self.pool)
            .await?.into();
        
        Ok(serde_json::json!({
            "total": total_count,
            "online": online_count,
            "offline": total_count - online_count
        }))
    }
    
    /// 获取账号统计信息
    pub async fn get_account_stats(&self) -> AppResult<serde_json::Value> {
        let active_count: i64 = sqlx::query_scalar!(
            "SELECT COUNT(*) FROM social_accounts WHERE status = 'active'"
        )
        .fetch_one(&self.pool)
        .await?.into();
        
        let total_count: i64 = sqlx::query_scalar!("SELECT COUNT(*) FROM social_accounts")
            .fetch_one(&self.pool)
            .await?.into();
        
        Ok(serde_json::json!({
            "total": total_count,
            "active": active_count,
            "inactive": total_count - active_count
        }))
    }
    
    /// 获取任务统计信息  
    pub async fn get_task_stats(&self) -> AppResult<serde_json::Value> {
        let completed_count: i64 = sqlx::query_scalar!(
            "SELECT COUNT(*) FROM tasks WHERE status = 'completed'"
        )
        .fetch_one(&self.pool)
        .await?.into();
        
        let running_count: i64 = sqlx::query_scalar!(
            "SELECT COUNT(*) FROM tasks WHERE status = 'running'"
        )
        .fetch_one(&self.pool)
        .await?.into();
        
        let failed_count: i64 = sqlx::query_scalar!(
            "SELECT COUNT(*) FROM tasks WHERE status = 'failed'"
        )
        .fetch_one(&self.pool)
        .await?.into();
        
        let total_count: i64 = sqlx::query_scalar!("SELECT COUNT(*) FROM tasks")
            .fetch_one(&self.pool)
            .await?.into();
        
        Ok(serde_json::json!({
            "total": total_count,
            "completed": completed_count,
            "running": running_count,
            "failed": failed_count,
            "pending": total_count - completed_count - running_count - failed_count
        }))
    }
}